﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Linkman_GetList]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Linkman_GetList];
GO
CREATE PROCEDURE [dbo].[sproc_Linkman_GetList]
@type         int,
@staffid     int
/*-----------------------------
功能： 得到联系人列表
参数：
    @type        int    :    0:所有联系人1:员工联系人2:客户联系人3:自定义联系人
    @staffid    int    :    用户id

-------------------------------*/
AS

    IF(@type=0)
        SELECT * FROM UDS_Linkman_LinkmanList
        WHERE Staff_ID=@staffid
    ELSE IF(@type=1)
        SELECT UDS_Staff.*,datediff(yy,UDS_Staff.birthday,getdate()) as Age,(case UDS_Staff.sex when 1 then '男' else '女' end) as SexName,Convert(nvarchar(10),UDS_Staff.RegistedDate,120) AS RQ,(SELECT Position_name FROM uds_Position WHERE Position_id = UDS_Staff_in_position.Position_id) AS Position_Name,(SELECT Position_ID FROM uds_Position WHERE Position_id = UDS_Staff_in_position.Position_id) AS Position_ID FROM UDS_Linkman_LinkmanList,UDS_STAFF,uds_staff_in_position 
        WHERE UDS_Linkman_LinkmanList.LinkmanID=UDS_Staff.Staff_ID AND UDS_Linkman_LinkmanList.Staff_ID=@staffid AND LinkmanType=1 and uds_staff.staff_id = uds_staff_in_position.staff_id
    ELSE IF(@type=2)
        SELECT UDS_CM_Linkman.*,UDS_CM_ClientInfo.Name AS UnitName FROM UDS_Linkman_LinkmanList,UDS_CM_Linkman,UDS_CM_ClientInfo
        WHERE UDS_CM_ClientInfo.ID=UDS_CM_Linkman.ClientID AND UDS_CM_ClientInfo.AddmanID=@staffid AND  UDS_Linkman_LinkmanList.LinkmanID=UDS_CM_Linkman.ID AND UDS_Linkman_LinkmanList.Staff_ID=@staffid AND LinkmanType=2
    ELSE IF(@type=3)
        SELECT UDS_Linkman_CustomLinkman.*,(case UDS_Linkman_CustomLinkman.Gender when 1 then '男' else '女' end) as SexName FROM UDS_Linkman_LinkmanList,UDS_Linkman_CustomLinkman
        WHERE UDS_Linkman_LinkmanList.LinkmanID=UDS_Linkman_CustomLinkman.ID AND UDS_Linkman_LinkmanList.Staff_ID=@staffid AND LinkmanType=3